﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class PositionCommandDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PositionCommandDAL()
        {

        }
        public PositionCommandInfo GetPositionCommandInfo(Nullable<int> Seq)
        {
            PositionCommandInfo Info = new PositionCommandInfo();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Seq", Seq);

                strSQL = "SELECT CMD_POS_SEQ, CMD_POS_REF, CMD_POS_DESCR, APPROVED_BY"
                        + ", APPROVED_NAME, APPROVED_POSITION, APPROVED_DATE, REF_NO"
                        + ", REF_DATE, CMD_STATUS, EMP_TYPE_SEQ, REF_NO, REF_DATE, REMARKS"
                    + " FROM HRM_MP_POSITION_COMMANDS"
                    + " WHERE RECORD_STATUS not like @DeleteRecord"
                        + " AND CMD_POS_SEQ = @Seq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    Info.Seq = rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0);
                    Info.CmdRefNo = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
                    Info.Description = rdr.IsDBNull(2) ? "" : rdr.GetString(2);
                    Info.ApprovedBy = rdr.IsDBNull(3) ? (int?)null : rdr.GetInt32(3);
                    Info.ApprovedName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    Info.ApprovedPosition = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
                    Info.ApprovedDate = rdr.IsDBNull(6) ? (DateTime?)null : rdr.GetDateTime(6);
                    Info.RefNo = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
                    Info.RefDate = rdr.IsDBNull(8) ? (DateTime?)null : rdr.GetDateTime(8);
                    Info.CmdStatusSeq = rdr.IsDBNull(9) ? "" : rdr.GetString(9);
                    Info.EmployeeTypeSeq = rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10);
                    Info.RefNo = rdr.IsDBNull(11) ? "" : rdr.GetString(11);
                    Info.RefDate = rdr.IsDBNull(12) ? (DateTime?)null : rdr.GetDateTime(12);
                    Info.Remarks = rdr.IsDBNull(13) ? "" : rdr.GetString(13);
                }

                return Info;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<PositionCommandInfo> LoadGridPositionCommands(Nullable<int> EmpTypeSeq)
        {
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PositionCommandInfo> infoList = new List<PositionCommandInfo>();

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT c.CMD_POS_SEQ, c.CMD_POS_REF, c.CMD_POS_DESCR, c.APPROVED_BY"
                        + ", c.APPROVED_POSITION, c.APPROVED_DATE, c.CMD_STATUS"
                        + ", c.REMARKS, rs.RECORD_STATUS_NAME, c.REF_NO, c.REF_DATE"
                        + ", c.REF_NO, c.REF_DATE, c.REMARKS, rs.CMD_STATUS_NAME"
                        + ", CASE WHEN c.APPROVED_BY IS NOT NULL "
                                + " THEN CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                                + " ELSE c.APPROVED_NAME END"
                                + " AS APPROVED_NAME"
                    + " FROM HRM_MP_POSITION_COMMANDS c"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.APPROVED_BY = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN MAS_RECORD_STATUSES rs ON c.CMD_STATUS = rs.RECORD_STATUS"
                    + " WHERE c.RECORD_STATUS not like @DeleteRecord"
                        + " AND c.EMP_TYPE_SEQ = @EmpTypeSeq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PositionCommandInfo Info = new PositionCommandInfo();
                        Info.Seq = Convert.IsDBNull(dr[i]["CMD_POS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["CMD_POS_SEQ"].ToString());
                        Info.CmdRefNo = Convert.IsDBNull(dr[i]["CMD_POS_REF"]) ? "" : (dr[i]["CMD_POS_REF"]).ToString();
                        Info.Description = Convert.IsDBNull(dr[i]["CMD_POS_DESCR"]) ? "" : (dr[i]["CMD_POS_DESCR"]).ToString();
                        Info.ApprovedBy = Convert.IsDBNull(dr[i]["APPROVED_BY"]) ? (int?)null : Int32.Parse(dr[i]["APPROVED_BY"].ToString());
                        Info.ApprovedName = Convert.IsDBNull(dr[i]["APPROVED_NAME"]) ? "" : (dr[i]["APPROVED_NAME"]).ToString();
                        Info.ApprovedPosition = Convert.IsDBNull(dr[i]["APPROVED_POSITION"]) ? "" : (dr[i]["APPROVED_POSITION"]).ToString();
                        Info.ApprovedDate = Convert.IsDBNull(dr[i]["APPROVED_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["APPROVED_DATE"].ToString());
                        Info.RefNo = Convert.IsDBNull(dr[i]["REF_NO"]) ? "" : (dr[i]["REF_NO"]).ToString();
                        Info.RefDate = Convert.IsDBNull(dr[i]["REF_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["REF_DATE"].ToString());
                        Info.CmdStatusSeq = Convert.IsDBNull(dr[i]["CMD_STATUS"]) ? "" : (dr[i]["CMD_STATUS"]).ToString();
                        Info.CmdStatusName = Convert.IsDBNull(dr[i]["CMD_STATUS_NAME"]) ? "" : (dr[i]["CMD_STATUS_NAME"]).ToString();
                        Info.Remarks = Convert.IsDBNull(dr[i]["REMARKS"]) ? "" : (dr[i]["REMARKS"]).ToString();

                        infoList.Add(Info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public void Insert(PositionCommandInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@CmdPosRef", Info.CmdRefNo);
                dbManager.AddParameters(1, "@CmdPosDescr", Info.Description);
                dbManager.AddParameters(2, "@ApprovedBy", Info.ApprovedBy);
                dbManager.AddParameters(3, "@Approved_name", Info.ApprovedName);
                dbManager.AddParameters(4, "@ApprovedPosition", Info.ApprovedPosition);
                dbManager.AddParameters(5, "@ApprovedDate", Info.ApprovedDate);
                dbManager.AddParameters(6, "@CmdStatus", Info.CmdStatusSeq);
                dbManager.AddParameters(7, "@EmpTypeSeq", Info.EmployeeTypeSeq);
                //dbManager.AddParameters(8, "@RefNo", Info.RefNo);
                dbManager.AddParameters(8, "@RefDate", Info.RefDate);
                dbManager.AddParameters(9, "@Remarks", Info.Remarks);
                dbManager.AddParameters(10, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(12, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_MP_POSITION_COMMANDS(CMD_POS_REF, CMD_POS_DESCR, APPROVED_BY"
                        + ", APPROVED_NAME, APPROVED_POSITION, APPROVED_DATE, CMD_STATUS, EMP_TYPE_SEQ"
                        + ", REF_DATE, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        //+ ", REF_NO, REF_DATE, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@CmdPosRef, @CmdPosDescr, @ApprovedBy, @Approved_name, @ApprovedPosition"
                        + ", @ApprovedDate, @CmdStatus, @EmpTypeSeq, @RefDate, @Remarks"
                        //+ ", @ApprovedDate, @CmdStatus, @EmpTypeSeq, @RefNo, @RefDate, @Remarks"
                        + ", @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(PositionCommandInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@CmdPosSeq", Info.Seq);
                dbManager.AddParameters(1, "@CmdPosRef", Info.CmdRefNo);
                dbManager.AddParameters(2, "@CmdPosDescr", Info.Description);
                dbManager.AddParameters(3, "@ApprovedBy", Info.ApprovedBy);
                dbManager.AddParameters(4, "@ApprovedName", Info.ApprovedName);
                dbManager.AddParameters(5, "@ApprovedPosition", Info.ApprovedPosition);
                dbManager.AddParameters(6, "@ApprovedDate", Info.ApprovedDate);
                dbManager.AddParameters(7, "@CmdStatus", Info.CmdStatusSeq);
                dbManager.AddParameters(8, "@EmpTypeSeq", Info.EmployeeTypeSeq);
                //dbManager.AddParameters(9, "@RefNo", Info.RefNo);
                dbManager.AddParameters(9, "@RefDate", Info.RefDate);
                dbManager.AddParameters(10, "@Remarks", Info.Remarks);
                dbManager.AddParameters(11, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(12, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITION_COMMANDS"
                        + " SET CMD_POS_REF = @CmdPosRef"
                        + ", CMD_POS_DESCR = @CmdPosDescr"
                        + ", APPROVED_BY = @ApprovedBy"
                        + ", APPROVED_NAME = @ApprovedName"
                        + ", APPROVED_POSITION = @ApprovedPosition"
                        + ", APPROVED_DATE = @ApprovedDate"
                        + ", CMD_STATUS = @CmdStatus"
                        + ", EMP_TYPE_SEQ = @EmpTypeSeq"
                        //+ ", REF_NO = @RefNo"
                        + ", REF_DATE = @RefDate"
                        + ", REMARKS = @Remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_POS_SEQ = @CmdPosSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();

                if (Info.CmdStatusSeq == SystemInfo._ApproveRecordStatus)
                {
                    strSQL = "CALL P_Auto_update_jobsite_commands(@CmdPosSeq)";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    dbManager.CommitTransaction();
                }
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(PositionCommandInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Info.Seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITION_COMMANDS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CMD_POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
